﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
//using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace RH.Domain
{
    public class UtilityDAO
    {
        //private static String _connectionString;
        //public static String ConnectionString
        //{
        //    get { return _connectionString; }
        //    set { _connectionString = value; }
        //}
        private MySqlConnection _Conn;

        public UtilityDAO()
        {
            ///Initialize a DB Connection from the Entity Framework
            AppEntities context = new AppEntities();
            var entityConnection = context.Connection as System.Data.EntityClient.EntityConnection;
            if (entityConnection != null)
            {
                ///NOTE: Very Funny without full Namespace this cannot be type casted :)
                _Conn = entityConnection.StoreConnection as MySql.Data.MySqlClient.MySqlConnection;
                if (_Conn.State != ConnectionState.Open)
                    _Conn.Open();
            }
        }
        /// <summary>
        /// Gets DataSet by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">The SQL String or the Name of the Stored Procedure</param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetDataSet(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {            
            DataSet ds = new DataSet("CustomeQueryResult");
            try
            {
                MySqlCommand command = new MySqlCommand(SQL, _Conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                MySqlDataAdapter da = new MySqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL String Equiped with Page Number PlaceHolders.
        /// Placeholder should be given for Start Record and End Record as {0} -> Start Record and {1} -> End Record
        /// </summary>
        /// <param name="SQL">The SQL String</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, false);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">Name of the Stored Procedure or the Full SQL Statement with the Placeholders for Start Record and End Record</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, isStoredProcedure);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        private DataSet getPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            totalRecord = 0;
            //int startRecord = (pageSize * (pageNumber - 1)) + 1;
            ///This Calculation is alsom MySQL Specific. MySQL Calculates Result set from 0 index instead of 1 as SQL Server
            int startRecord = (pageSize * (pageNumber - 1));
            ///endRecord is not needed in MySQL for Pagination, instead PageSize is required
            //int endRecord = pageSize * pageNumber; 
            DataSet ds = new DataSet("ResultSet");
            try
            {
                MySqlCommand command = new MySqlCommand();
                command.Connection = _Conn;
                if (isStoredProcedure)
                {
                    command.Parameters.AddWithValue("@StartRecord", startRecord);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    SQL = string.Format(SQL, startRecord, pageSize);
                    command.CommandType = CommandType.Text;
                }
                command.CommandText = SQL;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                    {
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));
                    }
                }
                MySqlDataAdapter da = new MySqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            if (ds != null)
            {
                ///MySQL Specific
                if (ds.Tables.Count > 1)
                {
                    object data = ds.Tables[1].Rows[0]["TotalRecord"];
                    totalRecord = ((data == null) || (data == DBNull.Value) || (data.ToString() == string.Empty)) ? 0 : Convert.ToInt32(data);
                }
            }
            return ds;
        }

        /// <summary>
        /// Gets Scalar Value Object by Executing an SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public object GetScalar(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {
            //SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                //if (conn.State != ConnectionState.Open)
                //    conn.Open();

                MySqlCommand command = new MySqlCommand(SQL, _Conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new MySqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Conn != null && _Conn.State == ConnectionState.Open)
                    _Conn.Close();
            }
            //return null;
        }

        /// <summary>
        /// Gets a Datatable for a given Lookup table Name
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="valueField"></param>
        /// <param name="textField"></param>
        /// <returns></returns>
        public DataTable GetLookUpData(String tableName, String valueField, String textField)
        {
            DataTable dt = new DataTable();
            DbParameter[] parameters = new[] {
                new DbParameter("@TableName", tableName),
                new DbParameter("@ValueField", valueField),
                new DbParameter("@TextField", textField)
            };
            dt = GetDataSet(DbObject.SP.GET_LOOKUP_DATA, parameters, true).Tables[0];
            return dt;
        }

        /// <summary>
        /// Backup the currently connected database in give file.
        /// Object & Data will be saved in text format.
        /// For details export info visit 
        /// http://www.codeproject.com/Articles/256466/MySqlBackup-NET?fid=1764817&df=90&mpp=10&sort=Position&spc=Tight&tid=4829974
        /// </summary>
        /// <param name="fileName"></param>
        public void BackupDatabase(String fileName)
        {
            using (_Conn)
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = _Conn;
                        mb.ExportToFile(fileName);
                    }
                }
            }
        }
    }
}
